        
using System;
using Dapper;
using System.Linq;
using Project.IData;
using Project.Model;
using System.Linq.Expressions;
using System.Collections.Generic;

namespace Project.Data 
{
    public partial class UserInfoData : BaseData<UserInfo>,IUserInfoData
    {
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="entity">插入数据对象</param>
        /// <returns>数据库插入条数</returns>
        public int Create(UserInfo entity)
        {
			string sql=@"insert into UserInfo(UserName, Age, Gender, Address, CreateTime, Phone, EmployeeId, ActionInfoId) values(:UserName, :Age, :Gender, :Address, :CreateTime, :Phone, :EmployeeId, :ActionInfoId)";
            using (var connection = Database.GetConnection())
            {
                return connection.Query<int>(sql, entity).FirstOrDefault();
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity">修改数据对象</param>
        /// <returns>修改成功条数</returns>
        public UserInfo Edit(UserInfo entity)
        {
            string sql = @"update UserInfo set UserName = :UserName, Age = :Age, Gender = :Gender, Address = :Address, CreateTime = :CreateTime, Phone = :Phone, EmployeeId = :EmployeeId, ActionInfoId = :ActionInfoId where UserInfoId = :UserInfoId";
            using (var connection = Database.GetConnection())
            {
                connection.Execute(sql, entity);
            }
            return entity;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity">用户对象，只传Id即可</param>
        /// <returns>删除的行数</returns>
        public int Delete(UserInfo entity)
        {
            string sql = "delete UserInfo where UserInfoId = :UserInfoId ";
            using (var connection = Database.GetConnection())
            {
                return connection.Execute(sql, entity);
            }
        }
        /// <summary>
        /// 获取所有的数据
        /// </summary>
        /// <returns></returns>
        public IEnumerable<UserInfo> LoadModel(Expression<Func<UserInfo,bool>> lambdaWhere)
        {
            string sql = "select u.UserInfoId,u.UserName,u.Age,CAST(u.Gender as int) as Gender,u.Address,u.CreateTime,u.Phone,u.EmployeeId,u.ActionInfoId ,e.EmployeeId,e.LoginName,e.LoginPwd,a.ActionInfoId,a.ActionName from UserInfo u left join Employee e on u.EmployeeId = e.EmployeeId  left join ActionInfo a on u.ActionInfoId = a.ActionInfoId "+ " where " + lambdaWhere.Body.ToString().Substring(1,lambdaWhere.Body.ToString().Length-2);

            using (var connection =Database.GetConnection())
            {
				 return  connection.Query<UserInfo, Employee, ActionInfo, UserInfo>(sql, (u, e, a) => {u.Employee = e;u.ActionInfo = a;return u;});
            }
        }
        /// <summary>
        /// 获取相应的分页数据
        /// </summary>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageCount">每页条数</param>
        /// <returns></returns>
        public IEnumerable<UserInfo> LoadModelByPage(int pageIndex, int pageCount,out int total, Expression<Func<UserInfo,bool>> lambdaWhere)
        {
var start = pageIndex * pageCount - pageCount;
            var end = pageIndex * pageCount;

            string sql = string.Format("select UserInfoId, UserName, Age, Gender, Address, CreateTime, Phone, EmployeeId EId, LoginName, LoginPwd, ActionInfoId AId, ActionName from (select row_number() over(order by u.UserInfoId desc) as rk, u.UserInfoId,u.UserName,u.Age,CAST(u.Gender as int) as Gender,u.Address,u.CreateTime,u.Phone , e.EmployeeId,e.LoginName,e.LoginPwd, a.ActionInfoId,a.ActionName from UserInfo u left join Employee e on u.EmployeeId = e.EmployeeId  left join ActionInfo a on u.ActionInfoId = a.ActionInfoId ) u where rk between {0} and {1}   and {2}",start,end, lambdaWhere.Body.ToString().Substring(1,lambdaWhere.Body.ToString().Length-2));

            using (var connection = Database.GetConnection())
            {
				var userList = connection.Query<UserInfo, Employee, ActionInfo, UserInfo>(sql, (u, e, a) => {u.Employee = e;u.ActionInfo = a;return u;}, splitOn: "EId,AId");				
                
				total = userList.Count();
                return userList;
            }
        }

    }
}
